from openpyxl import load_workbook
from tqdm import tqdm
import  csv
import json
import requests

'''
1. 根据 ItemGroupID 分组，对 Item 进行计算，相同 Group 下 Parent Item 数量
2. 当前 GroupID 下的 item 全部都是 2-3-3 格式

SELECT TOP(1000) COUNT(a02.Item),a02.ItemGroupID FROM Inventory.dbo.ItemPriceSetting ips WITH(NOLOCK)
INNER JOIN ItemMaintainNewegg.dbo.ARINVT02 a02 WITH(NOLOCK)
ON ips.ItemNUmber = a02.Item
WHERE ips.Active = '1' AND ips.CountryCode = 'USA' AND a02.ItemGroupID > 0 AND Item LIKE '__-___-___' AND a02.ParentItem IS NULL
GROUP BY a02.ItemGroupID
HAVING COUNT(a02.Item) BETWEEN 20 AND 30

1. 通过 IM_ItemProperty_DisplayStyle 获取有 property item
2. 只提取 Newegg Item 作为 main item (233,345 格式)
3. 通过 Acitve 字段判断 item 是否有效状态
SELECT TOP(1000) LTRIM(RTRIM(ips.ItemNumber)),LTRIM(RTRIM(ips.CountryCode)) FROM Inventory.dbo.ItemPriceSetting ips WITH(NOLOCK)
INNER JOIN CodeCenter.dbo.IM_ItemProperty_DisplayStyle ds WITH(NOLOCK)
ON ips.ItemNUmber = ds.ItemNUmber
WHERE ips.Active = '1' AND ips.CountryCode = 'USA' AND (ds.ItemNUmber LIKE '__-___-___' OR ds.ItemNUmber LIKE '___-____-_____')
ORDER BY ips.ItemNUmber DESC
'''

sourceFile="GroupID.xlsx"
wb=load_workbook(sourceFile)
sheet=wb['Sheet1']
sourceRows=sheet.max_row
sourceData=[]
for index in (range(sourceRows)):
    sourceData.append(sheet.cell(index+1,1).value)

sourceData=sourceData[1:]

#二维数组，为了能多行写入csv
toCsvList_10T30=[]
toCsvList_30T50=[]
toCsvList_50T100=[]
toCsvList_100UP=[]
for index_source,source in enumerate(tqdm(sourceData)):
    onedata_10T30 = []
    onedata_30T50=[]
    onedata_50T100 = []
    onedata_100UP = []

    url = "http://172.16.140.212:8367/hierarchyservice/hierarchyV2"
    headers = {'Accept': 'application/json', 'Content-Type': 'application/json'}
    request_body = {"CompanyCode": 1003,
                    "CountryCode": "USA",
                    "GroupID": source
                    }
    r= requests.post(url=url, data=json.dumps(request_body), headers=headers)

    # 提取 childrenTotalCount
    childCount=r.json().get("childrenTotalCount")

    # 提取范围 10 ~ 30 (不包含 30)
    if childCount>=10 and childCount<30:
        onedata_10T30.append(source)
    if onedata_10T30!=[]:
        toCsvList_10T30.append(onedata_10T30)

    # 提取范围 30 ~ 50 (不包含 50)
    if childCount>=30 and childCount<50:
        onedata_30T50.append(source)
    if onedata_30T50!=[]:
        toCsvList_30T50.append(onedata_30T50)

    # 提取范围 50 ~ 100 (不包含 100)
    if childCount>=50 and childCount<=100:
        onedata_50T100.append(source)
    if onedata_50T100!=[]:
        toCsvList_50T100.append(onedata_50T100)

    # 提取范围 100 以上
    if childCount>100:
        onedata_100UP.append(source)
    if onedata_100UP!=[]:
        toCsvList_100UP.append(onedata_100UP)

with open('ItemGroupID_10T30.csv','w',newline='')as file:
    op=csv.writer(file)
    op.writerows(toCsvList_10T30)

with open('ItemGroupID_30T50.csv','w',newline='')as file:
    op=csv.writer(file)
    op.writerows(toCsvList_30T50)

with open('ItemGroupID_50T100.csv','w',newline='')as file:
    op = csv.writer(file)
    op.writerows(toCsvList_50T100)

with open('ItemGroupID_100UP.csv','w',newline='')as file:
    op = csv.writer(file)
    op.writerows(toCsvList_100UP)